﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using MxWeiXinPF.Common;
using Aspose.Cells;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;
using MxWeiXinPF.DBUtility;

namespace MxWeiXinPF.Web.shopmgr.product
{
    public partial class productImport : MxWeiXinPF.Web.UI.ManagePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            //获取文件路径
            string filePath = this.FileUpload1.PostedFile.FileName;
            if (filePath != "")
            {
                if (filePath.Contains("xls"))//判断文件是否存在
                {
                    string savePath = Server.MapPath("~/upload/" + DateTime.Now.ToString("yyyyMMddhhmmss") + "/");
                    FileOperatpr(filePath, savePath);
                    FileUpload1.SaveAs(savePath + filePath);
                    DataOperator(filePath, savePath);  
                }
                else
                {
                    Response.Write("请检查您选择的文件是否为Excel文件！谢谢！");
                }
            }

            else
            {
                Response.Write("请先选择导入文件后，再执行导入！谢谢！");
            }
        }

        /// <summary>  
        /// 数据操作  
        /// </summary>  
        /// <param name="fileName"></param>  
        /// <param name="savePath"></param>  
        private void DataOperator(string fileName, string savePath)
        {
            //string myString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =  " + savePath + fileName + ";Extended Properties=Excel 8.0;";
            //OleDbConnection oconn = new OleDbConnection(myString);
            //oconn.Open();
            //DataSet ds = new DataSet();
            //OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", oconn);
            //oda.Fill(ds);
            //oconn.Close();
            //DataSetOperator(ds, savePath + fileName);

            Workbook workbook = new Workbook(savePath + fileName);

            Cells cells = workbook.Worksheets[0].Cells;

            DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, 18, false);

            if (dt != null)
            {
                DataSetOperator2(dt, savePath + fileName);
            }
            
        }

        /// <summary>  
        /// 数据集操作  
        /// </summary>  
        /// <param name="ds"></param>  
        private void DataSetOperator(DataTable dt, string filePath)
        {
            Model.manager weixin = GetAdminInfo();
            if (weixin == null)
            {
                return;
            }
            //SqlTransaction str = conn.BeginTransaction();//利用事务处理 防止中断  
            int k = 0;
            if (dt.Rows.Count < 1)
            {
                Response.Write("<script>alert('没有数据！')</script>");
                return;
            }
            try
            {
                List<string> sqllist = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string sqlStr = "insert into wx_shop_product (sku,productName,spec,ExpiryEndDate,Factory,costPrice,marketPrice,salePrice,ZhunZi,stock,extstr2,categoryId,shopid,wid,upselling,addDate) values";
                    sqlStr += "('" + dt.Rows[i][0].ToString() + "','";
                    sqlStr += dt.Rows[i][1].ToString() + "','";
                    sqlStr += dt.Rows[i][2].ToString() + "','";
                    sqlStr += dt.Rows[i][3].ToString() + "','";
                    sqlStr += dt.Rows[i][4].ToString() + "','";
                    sqlStr += dt.Rows[i][5].ToString() + "','";
                    sqlStr += dt.Rows[i][6].ToString() + "','";
                    sqlStr += dt.Rows[i][7].ToString() + "','";
                    sqlStr += dt.Rows[i][8].ToString() + "','";
                    sqlStr += dt.Rows[i][9].ToString() + "','";
                    sqlStr += dt.Rows[i][10].ToString() + "','";
                    sqlStr += dt.Rows[i][11].ToString() + "','";
                    sqlStr += weixin.id.ToString() + "','";
                    sqlStr += weixin.parentid.ToString() + "','";
                    sqlStr += "1" + "','";
                    sqlStr += DateTime.Now.ToString() + "')";
                    sqllist.Add(sqlStr);
                }

                k = DbHelperSQL.ExecuteSqlTran(sqllist);
            }
            catch (Exception ex)
            {
                Response.Write("发生异常,数据已回滚/n信息/n" + ex.Message);
            }
            finally
            {
                Response.Write("上传成功" + k + "条");
                File.Delete(filePath);
            }
        }

        /// <summary>  
        /// 数据集操作  
        /// </summary>  
        /// <param name="ds"></param>  
        private void DataSetOperator2(DataTable dt, string filePath)
        {
            Model.manager weixin = GetAdminInfo();
            if (weixin == null)
            {
                return;
            }
            //SqlTransaction str = conn.BeginTransaction();//利用事务处理 防止中断  
            int k = 0;
            if (dt.Rows.Count < 1)
            {
                Response.Write("<script>alert('没有数据！')</script>");
                return;
            }
            try
            {
                BLL.wx_shop_product bshop = new BLL.wx_shop_product();
                List<string> sqllist = new List<string>();
                string sqlStr = string.Empty;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string sku = dt.Rows[i][1].ToString();
                    DataSet ds = bshop.GetList(" materialNo ='" + sku + "' ");
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    { //存在已有的货号
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            sqlStr = "update wx_shop_product set ";
                            sqlStr += "categoryId ='" + dt.Rows[i][0].ToString() + "',";
                            sqlStr += "productName ='" + dt.Rows[i][2].ToString() + "',";
                            sqlStr += "spec ='" + dt.Rows[i][3].ToString() + "',";    
                            sqlStr += "ExpiryEndDate ='" + dt.Rows[i][4].ToString() + "',";
                            sqlStr += "Factory ='" + dt.Rows[i][5].ToString() + "',";
                            sqlStr += "costPrice ='" + dt.Rows[i][6].ToString() + "',";
                            sqlStr += "marketPrice ='" + dt.Rows[i][7].ToString() + "',";
                            sqlStr += "salePrice ='" + dt.Rows[i][8].ToString() + "',";
                            sqlStr += "ZhunZi ='" + dt.Rows[i][9].ToString() + "',";
                            sqlStr += "stock ='" + dt.Rows[i][10].ToString() + "',";
                            sqlStr += "SumQty ='" + dt.Rows[i][11].ToString() + "',";
                            sqlStr += "OneDayQty ='" + dt.Rows[i][12].ToString() + "',";
                            sqlStr += "IsSendMessage ='" + dt.Rows[i][13].ToString() + "',";
                            sqlStr += "extstr2 ='" + dt.Rows[i][14].ToString() + "', ";
                            sqlStr += "isRx ='" + dt.Rows[i][15].ToString() + "' ";
                            sqlStr += " where id = " + ds.Tables[0].Rows[j]["id"].ToString() + " and ShopID=39";
                            sqllist.Add(sqlStr);
                        }
                    }
                    else { 
                        //不存在货号
                        sqlStr = "insert into wx_shop_product (categoryId,sku,productName,spec,ExpiryEndDate,Factory,costPrice,marketPrice,salePrice,";
                        sqlStr += "ZhunZi,stock,SumQty,OneDayQty,IsSendMessage,extstr2,isRx,shopid,wid,upselling,addDate) values";
                        sqlStr += "('" + dt.Rows[i][0].ToString() + "','";
                        sqlStr += dt.Rows[i][1].ToString() + "','";
                        sqlStr += dt.Rows[i][2].ToString() + "','";
                        sqlStr += dt.Rows[i][3].ToString() + "','";
                        sqlStr += dt.Rows[i][4].ToString() + "','";
                        sqlStr += dt.Rows[i][5].ToString() + "','";
                        sqlStr += dt.Rows[i][6].ToString() + "','"; //costprice
                        sqlStr += dt.Rows[i][7].ToString() + "','";
                        sqlStr += dt.Rows[i][8].ToString() + "','";//salePrice
                        sqlStr += dt.Rows[i][9].ToString() + "','";//zhunzi
                        sqlStr += dt.Rows[i][10].ToString() + "','";//stock
                        sqlStr += dt.Rows[i][11].ToString() + "','";//SumQty
                        sqlStr += dt.Rows[i][12].ToString() + "','";//OneDayQty
                        sqlStr += dt.Rows[i][13].ToString() + "','";//IsSendMessage
                        sqlStr += dt.Rows[i][14].ToString() + "','";//extstr2
                        sqlStr += dt.Rows[i][15].ToString() + "','";//isRx
                        sqlStr += weixin.id.ToString() + "','";
                        sqlStr += weixin.parentid.ToString() + "','";
                        sqlStr += "1" + "','";
                        sqlStr += DateTime.Now.ToString() + "')";
                        sqllist.Add(sqlStr);
                    }
                }

                k = DbHelperSQL.ExecuteSqlTran(sqllist);
            }
            catch (Exception ex)
            {
                Response.Write("发生异常,数据已回滚/n信息/n" + ex.Message);
            }
            finally
            {
                Response.Write("上传成功" + k + "条");
                File.Delete(filePath);
            }
        }
        /// <summary>  
        /// 文件操作  
        /// </summary>  
        /// <param name="fileName"></param>  
        /// <param name="savePath"></param>  
        private void FileOperatpr(string fileName, string savePath)
        {
            if (!Directory.Exists(savePath))
            {
                Directory.CreateDirectory(savePath);
            }
            if (File.Exists(savePath + fileName))
            {
                File.Delete(savePath + fileName);
            }
        } 

        private void InputExcel(string pPath)
        {
            string conn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + pPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
            OleDbConnection oleCon = new OleDbConnection(conn);
            oleCon.Open();
            string Sql = "select * from [Sheet1$]";
            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon);
            DataSet ds = new DataSet();
            mycommand.Fill(ds, "[Sheet1$]");
            oleCon.Close();
            int count = ds.Tables["[Sheet1$]"].Rows.Count;
            for (int i = 0; i < count; i++)
            {
                string tRealName, tSex, tInClass, tQuestion, tAnswer;
                tRealName = ds.Tables["[Sheet1$]"].Rows[i]["姓名"].ToString().Trim();
                tSex = ds.Tables["[Sheet1$]"].Rows[i]["性别"].ToString().Trim();
                tInClass = ds.Tables["[Sheet1$]"].Rows[i]["班级"].ToString().Trim();
                tQuestion = ds.Tables["[Sheet1$]"].Rows[i]["学号"].ToString().Trim();
                tAnswer = ds.Tables["[Sheet1$]"].Rows[i]["初始密码"].ToString().Trim();
                string excelsql = "insert into tb_Users(RealName, Sex, InClass,Question,Answer) values ('" + tRealName + "','" + tSex + "','" + tInClass + "','" + tQuestion + "','" + tAnswer + "')";
                try
                {
                    //导入到SQL Server中
                    //DM dm = new DM();
                     
                    //dm.execsql(excelsql);
                    Response.Write("<script language='javascript'>Alert('数据导入成功!');window.location='Default.aspx'</script>");
                }
                catch (Exception)
                {
                    Response.Write("<script language='javascript'>Alert('数据导入失败!');window.location='Default.aspx'</script>");
                }
            }
        }

        //下载方法一
        protected void btndownfile_Click(object sender, EventArgs e)
        {
            /* 微软为Response对象提供了一个新的方法TransmitFile来解决使用Response.BinaryWrite 下载超过400mb的文件时导致Aspnet_wp.exe进程回收而无法成功下载的问题。 代码如下： */

            Response.ContentType = "application/x-zip-compressed";
            Response.AddHeader("Content-Disposition", "attachment;filename=商品导入模板.xls");
            string filename = Server.MapPath("../../upload/商品导入模板.xls");
            Response.TransmitFile(filename);
        }
    }
}